package ar.com.quickMart.database.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import ar.com.quickMart.database.StatisticsDao;

public class StatisticsDaoMySql extends BaseDaoMySql implements StatisticsDao{

	@Override
	public Integer getPurchaseCountToday(long branchId) {
		
		Connection connection;
	
		// Abro la conexion
		connection = connect();
	
		Integer count;
	
		// Creo el query
		String query = "SELECT COUNT(*) as count FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND Date(Fecha) = CURDATE() " +
				" AND E.Descripcion = 'Finalizada';";

	try {
		
		// Creo la sentencia y ejecuto el query
		PreparedStatement statement = connection.prepareStatement(query);

		statement.setLong(1, branchId);
		
		ResultSet result;
		
		result = statement.executeQuery();
		result = statement.getResultSet();
		
		if(result.next()) {
			count = result.getInt("count");
		}
		else{
			count = null;
		}
	}
	catch (Exception ex) {
		ex.printStackTrace();
		
		// Desconecto de la BDD
		disconnect(connection);
		

		return null;
	}
	
	// Desconecto de la BDD
	disconnect(connection);
	
	return count;
	}

	@Override
	public Integer getPurchaseCountThisMonth(long branchId) {
		Connection connection;
		
		// Abro la conexion
		connection = connect();
	
		Integer count;
	
		// Creo el query
		String query = "SELECT COUNT(*) as count" +
				" FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND E.Descripcion = 'Finalizada';";

		try {
		
			// Creo la sentencia y ejecuto el query
			PreparedStatement statement = connection.prepareStatement(query);

			statement.setLong(1, branchId);
			
			ResultSet result;
		
			result = statement.executeQuery();
			result = statement.getResultSet();
		
			if(result.next()) {
				count = result.getInt("count");
			}
			else{
				count = null;
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
		
			// Desconecto de la BDD
			disconnect(connection);
		

			return null;
		}
	
		// Desconecto de la BDD
		disconnect(connection);
	
		return count;
	}

	@Override
	public List<Long> getPurchaseIdsFinishedToday(long branchId) {
		Connection connection;
		
		// Abro la conexion
		connection = connect();
	
		List<Long> ids = new ArrayList<Long>();
	
		// Creo el query
		String query = "SELECT Lista_id " +
				" FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND Date(Fecha) = CURDATE() " +
				" AND E.Descripcion = 'Finalizada';";

		try {
		
			// Creo la sentencia y ejecuto el query
			PreparedStatement statement = connection.prepareStatement(query);

			statement.setLong(1, branchId);
			
			ResultSet result;
		
			result = statement.executeQuery();
			result = statement.getResultSet();
		
			while(result.next()) {
				ids.add(result.getLong("Lista_id"));
			}

		}
		catch (Exception ex) {
			ex.printStackTrace();
		
			// Desconecto de la BDD
			disconnect(connection);
		

			return null;
		}
	
		// Desconecto de la BDD
		disconnect(connection);
	
		if(ids.size() > 0){
			return ids;
		}
		else{
			return null;
		}
	}

	@Override
	public List<Long> getPurchaseIdsFinishedThisMonth(long branchId) {
		Connection connection;
		
		// Abro la conexion
		connection = connect();
	
		List<Long> ids = new ArrayList<Long>();
	
		// Creo el query
		String query = "SELECT Lista_id " +
				" FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND E.Descripcion = 'Finalizada';";

		try {
		
			// Creo la sentencia y ejecuto el query
			PreparedStatement statement = connection.prepareStatement(query);

			statement.setLong(1, branchId);
			
			ResultSet result;
		
			result = statement.executeQuery();
			result = statement.getResultSet();
		
			while(result.next()) {
				ids.add(result.getLong("Lista_id"));
			}

		}
		catch (Exception ex) {
			ex.printStackTrace();
		
			// Desconecto de la BDD
			disconnect(connection);
		

			return null;
		}
	
		// Desconecto de la BDD
		disconnect(connection);
	
		if(ids.size() > 0){
			return ids;
		}
		else{
			return null;
		}
	}

	@Override
	public Float getTotalMoneyToday(long branchId) {
		Connection connection;
		
		// Abro la conexion
		connection = connect();
	
		Float total;
	
		// Creo el query
		String query = "SELECT SUM(ImporteTotal) as total FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND Date(Fecha) = CURDATE() " +
				" AND E.Descripcion = 'Finalizada';";

		try {
		
			// Creo la sentencia y ejecuto el query
			PreparedStatement statement = connection.prepareStatement(query);

			statement.setLong(1, branchId);
			
			ResultSet result;
		
			result = statement.executeQuery();
			result = statement.getResultSet();
		
			if(result.next()) {
				total = result.getFloat("total");
			}
			else{
				total = null;
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
		
			// Desconecto de la BDD
			disconnect(connection);
		

			return null;
		}
	
		// Desconecto de la BDD
		disconnect(connection);
	
		return total;
	}

	@Override
	public Float getTotalMoneyThisMonth(long branchId) {
		Connection connection;
		
		// Abro la conexion
		connection = connect();
	
		Float total;
	
		// Creo el query
		String query = "SELECT SUM(ImporteTotal) as total " +
				" FROM compra C " +
				" JOIN EstadoDeCompra E ON C.EstadoDeCompra_id = E.id " +
				" JOIN Lista L ON C.Lista_id = L.idLista "+
				" WHERE L.Sucursal_id = ?" +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND MONTH(Fecha) = MONTH(CURDATE()) " +
				" AND E.Descripcion = 'Finalizada';";

		try {
		
			// Creo la sentencia y ejecuto el query
			PreparedStatement statement = connection.prepareStatement(query);

			statement.setLong(1, branchId);
			
			ResultSet result;
		
			result = statement.executeQuery();
			result = statement.getResultSet();
		
			if(result.next()) {
				total = result.getFloat("total");
			}
			else{
				total = null;
			}
		}
		catch (Exception ex) {
			ex.printStackTrace();
		
			// Desconecto de la BDD
			disconnect(connection);
		

			return null;
		}
	
		// Desconecto de la BDD
		disconnect(connection);
	
		return total;
	}



}
